package com.smart.business.service;

import com.smart.entity.business.ParamEntity;
import com.smart.model.exception.SmartException;
import com.smart.service.business.DynamicTableService;
import lombok.extern.slf4j.Slf4j;
import lombok.var;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
@Slf4j
@Service("dynamicTableService")
public class DynamicTableServiceImpl implements DynamicTableService {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Override
    public boolean createTable(String tableName) {
        // 验证输入
        if (StringUtils.isBlank(tableName)) {
            throw new IllegalArgumentException("表名不能为空");
        }

        // 白名单验证：只允许sys_company_数字_数字格式的表名
        if (!isValidTableName(tableName)) {
            throw new SmartException("无效的表名: " + tableName + " (只允许字母、数字和下划线)");
        }

        // 表名已通过白名单校验，拼接 SQL 是安全的
        String sql = String.format("CREATE TABLE IF NOT EXISTS `%s` ( " +
                        "`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键', " +
                        "`point_id` bigint NOT NULL COMMENT '关联点位id', " +
                        "`collector_number` bigint NOT NULL COMMENT '采集器号', " +
                        "`cumulative` decimal(10,2) NOT NULL COMMENT '累计值', " +
                        "`pick_time` datetime NOT NULL COMMENT '采集时间', " +
                        "PRIMARY KEY (`id`) , " +
                        "UNIQUE KEY `idx_point_collector_time` (`point_id`, `collector_number`, `pick_time`) " +
                        ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='%s';",
                tableName, tableName);

        try {
            jdbcTemplate.execute(sql);
            log.info("成功创建表: {}", tableName);
            return true;
        } catch (DataAccessException e) {
            throw new SmartException("创建表失败: " + tableName);
        }
    }

    /**
     * 校验表名是否合法（防止SQL注入）
     */
    private boolean isValidTableName(String tableName) {
        // 只允许sys_company_数字_数字格式的表名
        return tableName != null && tableName.matches("^sys_company_\\d+_\\d+$");
    }

    /**
     * 检查列是否存在
     * @param tableName 表名
     * @param columnName 列名
     * @return 是否存在
     */
    public boolean isColumnExists(String tableName, String columnName) {
        String sql = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS " +
                "WHERE TABLE_SCHEMA = DATABASE() " +
                "AND TABLE_NAME = ? " +
                "AND COLUMN_NAME = ?";

        Integer count = jdbcTemplate.queryForObject(sql, Integer.class, tableName, columnName);
        return count > 0;
    }

    /**
     * 添加列到公司表
     * @param tableName 表名
     * @param param 参数定义
     */
    @Transactional
    public void addColumnsInBatch(String tableName, List<ParamEntity> param) {
        param.forEach(paramEntity -> {
            String columnName = paramEntity.getCode();
            String dataType = getSqlDataType(paramEntity.getDataType());
            String comment = paramEntity.getName();

            if (isColumnExists(tableName, columnName)) {
                return;
            }

            String sql = "ALTER TABLE `" + tableName + "` ADD COLUMN `" + columnName + "` " +
                    dataType + " NULL COMMENT '" + comment + "'";

            jdbcTemplate.execute(sql);
        });


    }

    /**
     * 将参数数据类型转换为SQL数据类型
     * @param dataType 参数数据类型
     * @return SQL数据类型
     */
    private String getSqlDataType(String dataType) {
        switch (dataType.toLowerCase()) {
            case "float":
                return "DECIMAL(10,2)";
            case "int":
                return "BIGINT";
            case "boolean":
                return "TINYINT(1)";
            default:
                return "VARCHAR(64)";
        }
    }





}
